package com.situ.day18;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.junit.Test;

public class JDBCDemo {
	@Test
	public void test1() {
		//1、加载驱动Class.forName("");
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//2、获得连接对象Connection
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java1812?useUnicode=true&characterEncoding=UTF-8",
					"root", "1234");
			//3、写sql语句
			String sql = "SELECT id,`name`,age,gender FROM student;";
			//4、创建Statement
			Statement statement = connection.createStatement();
			//5、执行sql语句
			ResultSet resultSet = statement.executeQuery(sql);
			ArrayList<Student> list = new ArrayList<>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Test
	public void test2() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java1812?useUnicode=true&characterEncoding=UTF-8",
					"root", "1234");
			String sql = "SELECT id,`name`,age,gender FROM student;";
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			ArrayList<Student> list = new ArrayList<>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	@Test
	public void test3() {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,`name`,age,gender FROM student;";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			ArrayList<Student> list = new ArrayList<>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
	}
	@Test
	public void test4() {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from student where id = 2;";
			statement = connection.createStatement();
			int count = statement.executeUpdate(sql);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
	}
	@Test
	public void test5() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String name = "李四";
		String password = "123";
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT name,password FROM user WHERE name=? AND password=?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String userName = resultSet.getString("name");
				String userPassword = resultSet.getString("password");
				System.out.println(userName);
				System.out.println(password);
				System.out.println("登录成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Test
	public void test6() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String name = "李四";
		String password = "123";
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,`name`,password from user where name = ? and password = ?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String userName = resultSet.getString("name");
				String userPassword = resultSet.getString("password");
				System.out.println(userName);
				System.out.println(userPassword);
				System.out.println("成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Test
	public void test7() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int id = 3;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,`name`,age,className FROM student WHERE id = ?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int stuId = resultSet.getInt("id");
				String stuName = resultSet.getString("name");
				int stuAge = resultSet.getInt("age");
				String stuClassName = resultSet.getString("className");
				System.out.println("id:" + stuId);
				System.out.println("name:" + stuName);
				System.out.println("age:" + stuAge);
				System.out.println("className" + stuClassName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Test
	public void test8() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		String name = "无敌";
		int age = 26;
		String className = "Java1803";
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO student(`name`,age,className) VALUES (?,?,?);";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, age);
			preparedStatement.setString(3, className);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
